﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.UI;
using System.IO;
using System.Text;
using System.Data.Odbc;
using DBUtility;
using Usual;
using System.Data.OleDb;

namespace EMR.GeneralManager
{

public  class ExportDBF 
{
    private string _ErrInfo = "";

    private string tablename = "";


    private DbHelperOra sql_help;

    public ExportDBF()
    { 
    
        sql_help = new DbHelperOra();
    }


    public bool Create(string filename, string mTableName)
    {
        bool r = false;
        //filename = filename + mTableName;
        tablename = mTableName;
        string outconnstring = string.Format("Provider = Microsoft.Jet.OLEDB.4.0 ;Data Source ={0};Extended Properties=dBASE IV;", filename);
        string sqlt = "CREATE TABLE " + mTableName + "(" +
            "USERNAME  VARCHAR2(60)," +
    "YLFKFS  VARCHAR2(100)," +
    "JKKH  VARCHAR2(100)," +
    "ZYCS  VARCHAR2(100)," +
    "BAH  VARCHAR2(100)," +
    "XM  VARCHAR2(100)," +
    "XB  VARCHAR2(100)," +
    "CSRQ  VARCHAR2(12)," +
    "NL  NUMBER(10)," +
    "GJ  VARCHAR2(100)," +
    "BZYZSNL  NUMBER(4)," +
    "XSECSTZ  NUMBER(12,2)," +
    "XSERYTZ  NUMBER(12,2)," +
    "CSD  VARCHAR2(200)," +
    "GG  VARCHAR2(200)," +
    "MZ  VARCHAR2(100)," +
    "SFZH  VARCHAR2(100)," +
    "ZY  VARCHAR2(100)," +
    "HY  VARCHAR2(100)," +
    "XZZ  VARCHAR2(100)," +
    "DH  VARCHAR2(100)," +
    "YB1  VARCHAR2(100)," +
    "HKDZ  VARCHAR2(100)," +
    "YB2  VARCHAR2(100)," +
    "GZDWJDZ  VARCHAR2(100)," +
    "DWDH  VARCHAR2(100)," +
    "YB3  VARCHAR2(100)," +
    "LXRXM  VARCHAR2(100)," +
    "GX  VARCHAR2(100)," +
    "DZ  VARCHAR2(100)," +
    "DH2  VARCHAR2(100)," +
    "RYTJ  VARCHAR2(100)," +
    "RYSJ  VARCHAR2(12)," +
    "RYSJS  NUMBER(24)," +
    "RYKB  VARCHAR2(100)," +
    "RYBF  VARCHAR2(100)," +
    "ZKKB  VARCHAR2(100)," +
    "CYSJ  VARCHAR2(12)," +
    "CYSJS  NUMBER(24)," +
    "CYKB  VARCHAR2(100)," +
    "CYBF  VARCHAR2(100)," +
    "SJZYTS  VARCHAR2(100)," +
    "MZZD  VARCHAR2(200)," +
    "JBBM  VARCHAR2(100)," +
    "ZYZD  VARCHAR2(200)," +
    "JBDM  VARCHAR2(100)," +
    "RYBQ  VARCHAR2(100)," +
    "QTZD8  VARCHAR2(200)," +
    "JBDM8  VARCHAR2(100)," +
    "RYBQ8  VARCHAR2(100)," +
    "QTZD1  VARCHAR2(200)," +
    "JBDM1  VARCHAR2(100)," +
    "RYBQ1  VARCHAR2(100)," +
    "QTZD9  VARCHAR2(200)," +
    "JBDM9  VARCHAR2(100)," +
    "RYBQ9  VARCHAR2(100)," +
    "QTZD2  VARCHAR2(200)," +
    "JBDM2  VARCHAR2(100)," +
    "RYBQ2  VARCHAR2(100)," +
    "QTZD10  VARCHAR2(200)," +
    "JBDM10  VARCHAR2(100)," +
    "RYBQ10  VARCHAR2(100)," +
    "QTZD3  VARCHAR2(200)," +
    "JBDM3  VARCHAR2(100)," +
    "RYBQ3  VARCHAR2(100)," +
    "QTZD11  VARCHAR2(200)," +
    "JBDM11  VARCHAR2(100)," +
    "RYBQ11  VARCHAR2(100)," +
    "QTZD4  VARCHAR2(200)," +
    "JBDM4  VARCHAR2(100)," +
    "RYBQ4  VARCHAR2(100)," +
    "QTZD12  VARCHAR2(200)," +
    "JBDM12  VARCHAR2(100)," +
    "RYBQ12  VARCHAR2(100)," +
    "QTZD5  VARCHAR2(200)," +
    "JBDM5  VARCHAR2(100)," +
    "RYBQ5  VARCHAR2(100)," +
    "QTZD13  VARCHAR2(200)," +
    "JBDM13  VARCHAR2(100)," +
    "RYBQ13  VARCHAR2(100)," +
    "QTZD6  VARCHAR2(200)," +
    "JBDM6  VARCHAR2(100)," +
    "RYBQ6  VARCHAR2(100)," +
    "QTZD14  VARCHAR2(200)," +
    "JBDM14  VARCHAR2(100)," +
    "RYBQ14  VARCHAR2(100)," +
    "QTZD7  VARCHAR2(200)," +
    "JBDM7  VARCHAR2(100)," +
    "RYBQ7  VARCHAR2(100)," +
    "QTZD15  VARCHAR2(200)," +
    "JBDM15  VARCHAR2(100)," +
    "RYBQ15  VARCHAR2(100)," +
    "WBYY  VARCHAR2(254)," +
    "H23  VARCHAR2(100)," +
    "BLZD  VARCHAR2(100)," +
    "JBMM  VARCHAR2(100)," +
    "BLH  VARCHAR2(100)," +
    "YWGM  VARCHAR2(100)," +
    "GMYW  VARCHAR2(254)," +
    "SWHZSJ  VARCHAR2(100)," +
    "XX  VARCHAR2(100)," +
    "RH  VARCHAR2(100)," +
    "KZR  VARCHAR2(100)," +
    "ZRYS  VARCHAR2(100)," +
    "ZZYS  VARCHAR2(100)," +
    "ZYYS  VARCHAR2(100)," +
    "ZRHS  VARCHAR2(100)," +
    "JXYS  VARCHAR2(100)," +
    "SXYS  VARCHAR2(100)," +
    "BMY  VARCHAR2(100)," +
    "BAZL  VARCHAR2(100)," +
    "ZKYS  VARCHAR2(100)," +
    "ZKHS  VARCHAR2(100)," +
    "ZKRQ  VARCHAR2(12)," +
    "SSJCZBM1  VARCHAR2(100)," +
    "SSJCZRQ1  VARCHAR2(12)," +
    "SSJB1  VARCHAR2(100)," +
    "SSJCZMC1  VARCHAR2(200)," +
    "SZ1  VARCHAR2(100)," +
    "YZ1  VARCHAR2(100)," +
    "EZ1  VARCHAR2(100)," +
    "QKDJ1  VARCHAR2(100)," +
    "QKYHLB1  VARCHAR2(100)," +
    "MZFS1  VARCHAR2(100)," +
    "MZYS1  VARCHAR2(100)," +
    "SSJCZBM2  VARCHAR2(100)," +
    "SSJCZRQ2  VARCHAR2(12)," +
    "SSJB2  VARCHAR2(100)," +
    "SSJCZMC2  VARCHAR2(200)," +
    "SZ2  VARCHAR2(100)," +
    "YZ2  VARCHAR2(100)," +
    "EZ2  VARCHAR2(100)," +
    "QKDJ2  VARCHAR2(100)," +
    "QKYHLB2  VARCHAR2(100)," +
    "MZFS2  VARCHAR2(100)," +
    "MZYS2  VARCHAR2(100)," +
    "SSJCZBM3  VARCHAR2(100)," +
    "SSJCZRQ3  VARCHAR2(12)," +
    "SSJB3  VARCHAR2(100)," +
    "SSJCZMC3  VARCHAR2(200)," +
    "SZ3  VARCHAR2(100)," +
    "YZ3  VARCHAR2(100)," +
    "EZ3  VARCHAR2(100)," +
    "QKDJ3  VARCHAR2(100)," +
    "QKYHLB3  VARCHAR2(100)," +
    "MZFS3  VARCHAR2(100)," +
    "MZYS3  VARCHAR2(100)," +
    "SSJCZBM4  VARCHAR2(100)," +
    "SSJCZRQ4  VARCHAR2(12)," +
    "SSJB4  VARCHAR2(100)," +
    "SSJCZMC4  VARCHAR2(200)," +
    "SZ4  VARCHAR2(100)," +
    "YZ4  VARCHAR2(100)," +
    "EZ4  VARCHAR2(100)," +
    "QKDJ4  VARCHAR2(100)," +
    "QKYHLB4  VARCHAR2(100)," +
    "MZFS4  VARCHAR2(100)," +
    "MZYS4  VARCHAR2(100)," +
    "SSJCZBM5  VARCHAR2(100)," +
    "SSJCZRQ5  VARCHAR2(12)," +
    "SSJB5  VARCHAR2(100)," +
    "SSJCZMC5  VARCHAR2(200)," +
    "SZ5  VARCHAR2(100)," +
    "YZ5  VARCHAR2(100)," +
    "EZ5  VARCHAR2(100)," +
    "QKDJ5  VARCHAR2(100)," +
    "QKYHLB5  VARCHAR2(100)," +
    "MZFS5  VARCHAR2(100)," +
    "MZYS5  VARCHAR2(100)," +
    "SSJCZBM6  VARCHAR2(100)," +
    "SSJCZRQ6  VARCHAR2(12)," +
    "SSJB6  VARCHAR2(100)," +
    "SSJCZMC6  VARCHAR2(200)," +
    "SZ6  VARCHAR2(100)," +
    "YZ6  VARCHAR2(100)," +
    "EZ6  VARCHAR2(100)," +
    "QKDJ6  VARCHAR2(100)," +
    "QKYHLB6  VARCHAR2(100)," +
    "MZFS6  VARCHAR2(100)," +
    "MZYS6  VARCHAR2(100)," +
    "SSJCZBM7  VARCHAR2(100)," +
    "SSJCZRQ7  VARCHAR2(12)," +
    "SSJB7  VARCHAR2(100)," +
    "SSJCZMC7  VARCHAR2(200)," +
    "SZ7  VARCHAR2(100)," +
    "YZ7  VARCHAR2(100)," +
    "EZ7  VARCHAR2(100)," +
    "QKDJ7  VARCHAR2(100)," +
    "QKYHLB7  VARCHAR2(100)," +
    "MZFS7  VARCHAR2(100)," +
    "MZYS7  VARCHAR2(100)," +
    "LYFS  VARCHAR2(100)," +
    "YZZY_YLJG  VARCHAR2(200)," +
    "WSY_YLJG  VARCHAR2(200)," +
    "SFZZYJH  VARCHAR2(100)," +
    "MD  VARCHAR2(100)," +
    "RYQ_T  NUMBER(12)," +
    "RYQ_XS  NUMBER(24)," +
    "RYQ_F  NUMBER(12)," +
    "RYH_T  NUMBER(12)," +
    "RYH_XS  NUMBER(24)," +
    "RYH_F  NUMBER(12)," +
    "ZFY  NUMBER(12,2)," +
    "ZFJE  NUMBER(12,2)," +
    "YLFUF  NUMBER(12,2)," +
    "ZLCZF  NUMBER(12,2)," +
    "HLF  NUMBER(12,2)," +
    "QTFY  NUMBER(12,2)," +
    "BLZDF  NUMBER(12,2)," +
    "SYSZDF  NUMBER(12,2)," +
    "YXXZDF  NUMBER(12,2)," +
    "LCZDXMF  NUMBER(12,2)," +
    "FSSZLXMF  NUMBER(12,2)," +
    "WLZLF  NUMBER(12,2)," +
    "SSZLF  NUMBER(12,2)," +
    "MAF  NUMBER(12,2)," +
    "SSF  NUMBER(12,2)," +
    "KFF  NUMBER(12,2)," +
    "ZYZLF  NUMBER(12,2)," +
    "XYF  NUMBER(12,2)," +
    "KJYWF  NUMBER(12,2)," +
    "ZCYF  NUMBER(12,2)," +
    "ZCYF1  NUMBER(12,2)," +
    "XF  NUMBER(12,2)," +
    "BDBLZPF  NUMBER(12,2)," +
    "QDBLZPF  NUMBER(12,2)," +
    "NXYZLZPF  NUMBER(12,2)," +
    "XBYZLZPF  NUMBER(12,2)," +
    "HCYYCLF  NUMBER(12,2)," +
    "YYCLF  NUMBER(12,2)," +
    "YCXYYCLF  NUMBER(12,2)," +
    "QTF  NUMBER(12,2)" +
            ")";
        OleDbConnection outConn = new OleDbConnection(outconnstring);
        OleDbCommand dc = outConn.CreateCommand();
        try
        {

            outConn.Open();
            dc.CommandType = CommandType.Text;
            dc.CommandText = sqlt;
            dc.ExecuteNonQuery();
            r = true;

        }
        catch (Exception c)
        {
            _ErrInfo = c.Message;
        }
        finally
        {
            dc.Dispose();
            if (outConn.State == System.Data.ConnectionState.Open)
                outConn.Close();
            outConn.Dispose();
        }
        return r;
    }
    public void SaveDBF(string pathstr, DateTime dtFrom, DateTime dtTo)
    {
        #region 创建dbf副本
        string _f = "stu_info";
        //string tbl = pathstr;//Server.MapPath("/" + _f + ".dbf");
        string fileTemName = _f + "_" + DateTime.Now.ToShortDateString();
        string filetemPath = pathstr + "N041_"+Set.STRING_HOSPITAL_NAME+".dbf";//Server.MapPath("/dbf/" + fileTemName + ".dbf");
        //File.Copy(tbl, filetemPath, true);
        #endregion
        #region 创建连接
        System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection();
        string table = filetemPath;
        string connStr = @"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + table + ";Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO";
        conn.ConnectionString = connStr;
        conn.Open();
        #endregion
        #region 写入

        string strQuery = "SELECT * FROM PATIENTS WHERE to_char(pa_leavehospital_bookintime,'YYYY-MM-DD') between '" + dtFrom.ToShortDateString() + " ' and '" + dtTo.ToShortDateString() + "'";//string.Format("SELECT * FROM PATIENTS WHERE PA_BINGAN_CODE='{0}'");
        DataSet ds = sql_help.Query(strQuery);

        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            OdbcCommand cmd = new OdbcCommand();
            cmd.Connection = conn;
              //-机构名称-医疗付款方式-健康卡号-住院次数-病案号-姓名-性别-出生日期-年龄-国籍-(年龄不足1周岁的)年龄(月)-新生儿出生体重(克)-新生儿入院体重(克）-出生地-籍贯-民族-身份证号-职业-婚姻-现住址
            string sql = "insert into " + table + "([USERNAME],[YLFKFS],[JKKH],[ZYCS],[BAH],[XM],[XB],[CSRQ],[NL],[GJ],[BZYZSNL],[XSECSTZ],[XSERYTZ],[CSD],[GG],[MZ],[SFZH],[ZY],[HY],[XZZ]," +
                //-电话-邮编-户口地址-邮编-工作单位及地址-单位电话-邮编-联系人姓名-关系-地址-电话-入院途径-入院时间-时-入院科别-入院病房-转科科别-出院时间--时-出院科别-出院病房-实际住院(天)-门(急)诊诊断-疾病编码-主要诊断-疾病编码
               " ([DH],[YB1],[HKDZ],[YB2],[GZDWJDZ],[DWDH],[YB3],[LXRXM],[GX],[DZ],[DH2],[RYTJ],[RYSJ],[RYSJS],[RYKB],[RYBF],[ZKKB],[CYSJ],[CYSJS],[CYKB],[CYBF],[SJZYTS],[MZZD],[JBBM],[ZYZD],[JBDM]," +
                //-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断
               " ([RYBQ],[QTZD8],[JBDM8],[RYBQ8],[QTZD1],[JBDM1],[RYBQ1],[QTZD9],[JBDM9],[RYBQ9],[QTZD2],[JBDM2],[RYBQ2],[QTZD10],[JBDM10],[RYBQ10],[QTZD3],[JBDM3],[RYBQ3],[QTZD11],[JBDM11],[RYBQ11],[QTZD4],[JBDM4],[RYBQ4],[QTZD12]," +
                //-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-其他诊断-疾病编码-入院病情-中毒的外部原因-疾病编码-病理诊断出-疾病编码-病理号-药物过敏
               " ([JBDM12],[RYBQ12],[QTZD5],[JBDM5],[RYBQ5],[QTZD13],[JBDM13],[RYBQ13],[QTZD6],[JBDM6],[RYBQ6],[QTZD14],[JBDM14],[RYBQ14],[QTZD7],[JBDM7],[RYBQ7],[QTZD15],[JBDM15],[RYBQ15],[WBYY],[H23],[BLZD],[JBMM],[BLH],[YWGM]," +
                //=过敏药物疾病-死亡患者尸检-血型-Rh-科主任-主任（副主任）医-主治医师病理号死亡患者尸检-住院医师出院情况入院病情-责任护士-进修医师住-实习医师-编码员-病案质量-质控医师-质控护士-质控日期-手术及操作编码-手术及操作日期-手术级别-手术及操作名称-术者-I助-II助-切口等级-切口愈合类别-麻醉方式
               " ([GMYW],[SWHZSJ],[XX],[RH],[KZR],[ZRYS],[ZZYS],[ZYYS],[ZRHS],[JXYS],[SXYS],[BMY],[BAZL],[ZKYS],[ZKHS],[ZKRQ],[SSJCZBM1],[SSJCZRQ1],[SSJB1],[SSJCZMC1],[SZ1],[YZ1],[EZ1],[QKDJ1],[QKYHLB1],[MZFS1]," +
                //-麻醉医师-手术及操作编码-手术及操作日期-手术级别-手术及操作名称-术者-I助-II助-切口等级-切口愈合类别-麻醉方式-麻醉医师-手术及操作编码-手术及操作日期-手术级别-手术及操作名称-术者-I助-II助-切口等级-切口愈合类别-麻醉方-麻醉医师-手术及操作编码-手术及操作日期-手术级别-手术及操作名称-术者-I助-II助-切口等级-切口愈合类别-麻醉方式-情况麻醉医师-手术及操作编码-手术及操作日期-手术级别-手术及操作名称-术者-I助-II助-切口等级-切口愈合类别-麻醉方式-麻醉医师-手术及操作编码-手术及操作日期-手术级别-手术及操作名称-术者-I助-II助
               " ([MZYS1],[SSJCZBM2],[SSJCZRQ2],[SSJB2],[SSJCZMC2],[SZ2],[YZ2],[EZ2],[QKDJ2],[QKYHLB2],[MZFS2],[MZYS2],[SSJCZBM3],[SSJCZRQ3],[SSJB3],[SSJCZMC3],[SZ3],[YZ3],[EZ3],[QKDJ3],[QKYHLB3],[MZFS3],[MZYS3],[SSJCZBM4],[SSJCZRQ4],[SSJB4]," +
                //- 手术及操作名称-术者-I助-II助-切口等级-切口愈合类别-麻醉方式-情况麻醉医师-手术及操作编码-手术及操作日期-手术级别-手术及操作名称-术者-I助-II助-切口等级-切口愈合类别-麻醉方式-麻醉医师-手术及操作编码-手术及操作日期-手术级别-手术及操作名称-术者-I助-II助
               " ([SSJCZMC4],[SZ4],[YZ4],[EZ4],[QKDJ4],[QKYHLB4],[MZFS4],[MZYS4],[SSJCZBM5],[SSJCZRQ5],[SSJB5],[SSJCZMC5],[SZ5],[YZ5],[EZ5],[QKDJ5],[QKYHLB5],[MZFS5],[MZYS5],[SSJCZBM6],[SSJCZRQ6],[SSJB6],[SSJCZMC6],[SZ6],[YZ6],[EZ6]," +
                //-切口等级-切口愈合类别-麻醉方式-麻醉医师-手术及操作编码-手术及操作日期-手术级别-手术及操作名称-术者-I助-II助-切口等级-切口愈合类别-麻醉方式-麻醉医师-离院方式-医嘱转院，拟接收医疗机构名称-医嘱转社区卫生服务机构/乡镇卫生院，拟接收医疗机构名称-是否有出院31天内再住院计划手术情况-目的-颅脑损伤患者昏迷入院前时间：天-颅脑损伤患者昏迷入院前时间：小时-颅脑损伤患者昏迷入院前时间：分
               " ([QKDJ6],[QKYHLB6],[MZFS6],[MZYS6],[SSJCZBM7],[SSJCZRQ7],[SSJB7],[SSJCZMC7],[SZ7],[YZ7],[EZ7],[QKDJ7],[QKYHLB7],[MZFS7],[MZYS7],[LYFS],[YZZY_YLJG],[WSY_YLJG],[SFZZYJH],[MD],[RYQ_T],[RYQ_XS],[RYQ_F]" +
                //颅脑损伤患者昏迷入院后时间：天-颅脑损伤患者昏迷入院后时间：小时-颅脑损伤患者昏迷入院后时间：分-住院费用(元)：总费用-自付金额-综合医疗服务类：(1)一般医疗服务费-一般治疗操作费-护理费住院费-其他费用-诊断类：(5)病理诊断费-实验室诊断费-影像学诊断费-临床诊断项目费-治疗类：(9)非手术治疗项目费-临床物理治疗费-手术治疗费-麻醉费-手术费-康复类：(11)康复费-中医类:(12)中医治疗费-西药类:(13)西药费-抗菌药物费-中药类:(14)中成药费-中草药费
               " ([RYH_T],[RYH_XS],[RYH_F],[ZFY],[ZFJE],[YLFUF],[ZLCZF],[HLF],[QTFY],[BLZDF],[SYSZDF],[YXXZDF],[LCZDXMF],[FSSZLXMF],[WLZLF],[SSZLF],[MAF],[SSF],[KFF],[ZYZLF],[XYF],[KJYWF],[ZCYF],[ZCYF1]" +
                //血液和血液制品类:(16)血费白蛋白类制品费-球蛋白类制品费-凝血因子类制品费-细胞因子类制品费-耗材类:(21)检查用一次性医用材料费(22)治疗用一次性医用材料费(23)手术用一次性医用材料费其他类：(24)其他费
               " ([XF],[BDBLZPF],[QDBLZPF],[NXYZLZPF],[XBYZLZPF],[HCYYCLF],[YYCLF],[YCXYYCLF],[QTF] " +
                //第一行************************************************
               " values('" + Set.STRING_HOSPITAL_NAME + "','" + GetPayType(ds.Tables[0].Rows[i]["AR_FP_MEDICALPAYMENT"].ToString()) + "','" + ds.Tables[0].Rows[i]["AR_FP_MEDICALCARD"].ToString() + "','" + ds.Tables[0].Rows[i]["PA_BEINHOSPITAL_GREE"].ToString() + "','" +
               ds.Tables[0].Rows[i]["PA_BINGAN_CODE"].ToString() + "','" + ds.Tables[0].Rows[i]["PA_NAME"].ToString() + "','" + ds.Tables[0].Rows[i]["PA_SEX"].ToString() + "','" + ds.Tables[0].Rows[i]["N_NAME"].ToString() + "','" +
                Tools.GetAge(Convert.ToDateTime(ds.Tables[0].Rows[i]["PA_PRITHTIME"].ToString()), DateTime.Now) + "','" + ds.Tables[0].Rows[i]["AR_FP_IO_TREATMENTS"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_IO_TREATMENTS"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_BIRTHPLACE"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_CLINICPHYSICIANS"].ToString() + "','" + ds.Tables[0].Rows[i]["nname"].ToString() + "','" + ds.Tables[0].Rows[i]["PA_IDENTITYCARD"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_PROFESSIONAL"].ToString() + "','" + GetMarry(ds.Tables[0].Rows[i]["AR_FP_MARRIAGE"].ToString()) + "','" + ds.Tables[0].Rows[i]["AR_FP_ACCOUNT_ADDRESS"].ToString() + "','" +
                //第二行************************************************
                ds.Tables[0].Rows[i]["AR_FP_ACCOUNT_TELEPHONE"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_ACCOUNT_ZIP"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_CATEGORY"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_AL_ST_PHARMACEUTICAL"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_ACCOUNT_ZIP"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_CATEGORY"].ToString() + "','" +
                ds.Tables[0].Rows[i]["PA_WORK_UNIT"].ToString() + "','" + ds.Tables[0].Rows[i]["PA_W_PHONE"].ToString() + "','" + ds.Tables[0].Rows[i]["PA_W_POSTALCODE"].ToString() + "','" +
                ds.Tables[0].Rows[i]["PA_LINKMAN_NAME"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_LINKMAN_RELATION"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_LINKMAN_ADDRESS"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_LINKMAN_TELEPHONE"].ToString() + "','" + GetInType(ds.Tables[0].Rows[i]["PA_PATIENT_SOURCE"].ToString()) + "','" + ds.Tables[0].Rows[i]["PA_RY_TIME"].ToString() + "'," +
                DateTime.Parse(ds.Tables[0].Rows[i]["PA_RY_TIME"].ToString()).ToString("HH") + ",'" + ds.Tables[0].Rows[i]["depname"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_HOSPITALWARDS"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_AL_REGULATESECTIONS"].ToString() + "','" + ds.Tables[0].Rows[i]["PA_LEAVEHOSPITAL_PATIENT"].ToString() + "'," + DateTime.Parse(ds.Tables[0].Rows[i]["PA_LEAVEHOSPITAL_PATIENT"].ToString()).ToString("HH") + ",'" +
                ds.Tables[0].Rows[i]["depcyname"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_DISCHARGEWARD"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_CATEGORY"].ToString() + "','" +
                getDays(DateTime.Parse(ds.Tables[0].Rows[i]["PA_LEAVEHOSPITAL_PATIENT"].ToString()), DateTime.Parse(ds.Tables[0].Rows[i]["PA_RY_TIME"].ToString())) + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_WM_DIAGNOSTIC"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_DISCHARGEMANNER"].ToString() + 
                //疾病编码：主要诊断-疾病编码-入院病情
                GetZD(ds.Tables[0].Rows[i]["PA_BINGAN_CODE"].ToString(), ds.Tables[0].Rows[i]["PA_BINGAN_CODE"].ToString(), ds.Tables[0].Rows[i]["PA_BEINHOSPITAL_GREE"].ToString()) + "','" +
                //ds.Tables[0].Rows[i]["AR_FP_AL_CATEGORY"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_AL_TCM_PRATADDIAGNOSES"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_TCM_PRATADDIAGNOSES"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_AL_COURTTREATMENT"].ToString() + "','" +
                getXX(ds.Tables[0].Rows[i]["AR_FP_AL_HOSPITALCONDITIONS"].ToString()) + "','" + getRH(ds.Tables[0].Rows[i]["AR_FP_PC_BTFEE"].ToString()) + "','" + ds.Tables[0].Rows[i]["AR_FP_DIRECTOR"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_DEPUTYDIRECTOR"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_TA_PHYSICIAN"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_RESIDENCY"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_FURTHERPHYSICIANS"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_GRADUATEINTERNS"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_INTERN"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_CODERS"].ToString() + "','" +
                //病案质量
                getBAZL(ds.Tables[0].Rows[i]["AR_FP_MEDICALQUALITY"].ToString()) + "','" + ds.Tables[0].Rows[i]["AR_FP_QCPHYSICIANS"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_QCNURSES"].ToString() + "','" + 
                DateTime.Parse(ds.Tables[0].Rows[i]["AR_FP_RSDATE"].ToString())+ "','" +
                //手术信息
                GetSSInfo(ds.Tables[0].Rows[i]["PA_BINGAN_CODE"].ToString(), ds.Tables[0].Rows[i]["PA_BINGAN_CODE"].ToString(), ds.Tables[0].Rows[i]["PA_BEINHOSPITAL_GREE"].ToString()) + "','" +
                getLeaveType(ds.Tables[0].Rows[i]["AR_FP_ANAUTOPSY"].ToString()) + "','" + //离院方式
                ds.Tables[0].Rows[i]["AR_FP_RESCUE"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_RESCUESUCCESSNUMBER"].ToString() + "','" +
                //出院之后手术计划
                getHaveIS(ds.Tables[0].Rows[i]["AR_FP_RH"].ToString()) + "','" + ds.Tables[0].Rows[i]["AR_FP_SAVINGMETHOD"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_FOLLOW_UPPERIOD"].ToString().Split('_')[0] + "','" + ds.Tables[0].Rows[i]["AR_FP_FOLLOW_UPPERIOD"].ToString().Split('_')[1] + "','" + ds.Tables[0].Rows[i]["AR_FP_FOLLOW_UPPERIOD"].ToString().Split('_')[2] + "','" +
                ds.Tables[0].Rows[i]["AR_FP_BLOODTRANSFUSION"].ToString().Split('_')[0] + "','" + ds.Tables[0].Rows[i]["AR_FP_BLOODTRANSFUSION"].ToString().Split('_')[1] + "','" + ds.Tables[0].Rows[i]["AR_FP_BLOODTRANSFUSION"].ToString().Split('_')[2] + "','" +
                //费用
                ds.Tables[0].Rows[i]["AR_FP_PC_TOTAL"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_TREATMENT"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_BED"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_PC_OXYGENFEE"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_NURSE"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_OTHEREXPENSES"].ToString() + "','" +
                //诊断类：
                ds.Tables[0].Rows[i]["AR_FP_PC_INSPECTION"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_TEST"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_RADIATIONFEE"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_PC_DELIVERYFEE"].ToString() + "','" +
                //治疗类:
                ds.Tables[0].Rows[i]["AR_FP_PC_BABYFEE"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_SITHWHFEE"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_PC_THE"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_ANESTHESIAFEE"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_OPERATION"].ToString() + "','" +
                //康复类：
                ds.Tables[0].Rows[i]["AR_FP_PC_OTHEREXPENSE1"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_OTHEREXPENSE2"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_WMFEE"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_PC_KJYWFY"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_PC_IFEXPENSES"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_CHMFEE"].ToString() + "','" +
                //血液和血液制品类:
                ds.Tables[0].Rows[i]["AR_FP_PC_XF"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_BDBLZPF"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_QDBLZPF"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_PC_NXYZLZPF"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_XBYZLZPF"].ToString() + "','" +
                //耗材类:
                ds.Tables[0].Rows[i]["AR_FP_PC_HC_JC"].ToString() + "','" + ds.Tables[0].Rows[i]["AR_FP_PC_HC_ZL"].ToString() + "','" +
                ds.Tables[0].Rows[i]["AR_FP_PC_HC_SS"].ToString() + "','" +
                //其他：
                ds.Tables[0].Rows[i]["AR_FP_PC_OTHEREXPENSE3"].ToString() + "')"; 
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            //更新IsStuInfo＝1
            // db.ExecuteSql("update bm_zhanghao set IsStuInfo=1 where id=" + ds.Tables[0].Rows[i]["id"].ToString());
        }
        ds.Clear();
        ds.Dispose();
        conn.Close();
        conn.Dispose();
        #endregion
        #region 弹出导出对话框

        //Response.ContentType = "application/x-zip-compressed";
        //Response.AddHeader("Content-Disposition", "attachment;filename=学生信息导入_" + DateTime.Now.ToLongDateString() + ".dbf");
        string filename = filetemPath;
        //Response.TransmitFile(filename);

        #endregion
    }
    /// <summary>
    /// 是否有
    /// </summary>
    /// <param name="xx"></param>
    /// <returns></returns>
    private string getHaveIS(string xx)
    {
        string strtempe = string.Empty;
        switch (xx)
        {
            case "1": strtempe = "无"; break;
            case "2": strtempe = "有"; break;
            default: strtempe = "无";
                break;
        }
        return strtempe;
    }

    /// <summary>
    /// 离院方式
    /// </summary>
    /// <param name="cy"></param>
    /// <param name="ry"></param>
    /// <returns></returns>
    private string getLeaveType(string xx)
    {
        string strtempe = string.Empty;
        switch (xx)
        {
            case "1": strtempe = "医嘱离院"; break;
            case "2": strtempe = "医嘱转院"; break;
            case "3": strtempe = "医嘱转社区服务机构/乡镇卫生院"; break;
            case "4": strtempe = "非医嘱离院"; break;
            case "5": strtempe = "死亡"; break;
            case "6": strtempe = "其他"; break;
            default: strtempe = "其他";
                break;
        }
        return strtempe;
    }

    /// <summary>
    /// 病案质量
    /// </summary>
    /// <param name="cy"></param>
    /// <param name="ry"></param>
    /// <returns></returns>
    private string getBAZL(string xx)
    {
        string strtempe = string.Empty;
        switch (xx)
        {
            case "1": strtempe = "甲"; break;
            case "2": strtempe = "乙"; break;
            case "3": strtempe = "丙"; break;
            default: strtempe = "不详";
                break;
        }
        return strtempe;
    }

    /// <summary>
    /// RH
    /// </summary>
    /// <param name="cy"></param>
    /// <param name="ry"></param>
    /// <returns></returns>
    private string getRH(string xx)
    {
        string strtempe = string.Empty;
        switch (xx)
        {
            case "1": strtempe = "阳"; break;
            case "2": strtempe = "阴"; break;
            case "3": strtempe = "不详"; break;
            case "4": strtempe = "未查"; break;
            default: strtempe = "不详";
                break;
        }
        return strtempe;
    }

    /// <summary>
    /// 血型
    /// </summary>
    /// <param name="cy"></param>
    /// <param name="ry"></param>
    /// <returns></returns>
    private string getXX(string xx)
    {
        string strtempe = string.Empty;
        switch (xx)
        {
            case "1": strtempe = "A"; break;
            case "2": strtempe = "B"; break;
            case "3": strtempe = "O"; break;
            case "4": strtempe = "AB"; break;
            case "5": strtempe = "不详"; break;
            case "6": strtempe = "未查"; break;
            default: strtempe = "不详";
                break;
        }
        return strtempe;
    }

    /// <summary>
    /// 住院天数
    /// </summary>
    /// <returns></returns>
    private int getDays(DateTime cy, DateTime ry)
    {
        try
        {
            System.TimeSpan ts = cy - ry;
            return ts.Days + 1;
        }
        catch (System.Exception ex)
        {
            throw new Exception("请检查入院时间、出院时间是否正确！");
        }
    }
    /// <summary>
    /// 医疗拨付方式
    /// </summary>
    /// <param name="id"></param>
    /// <param name="tbl"></param>
    /// <param name="fld"></param>
    /// <returns></returns>
    private string GetPayType(string typeid)
    {
        string strtempe = string.Empty;
        switch (typeid)
        {
            case "1": strtempe = "社会基本医疗保险(补充保险、特大病保险)"; break;
            case "2": strtempe = "商业保险"; break;
            case "3": strtempe = "资费医疗"; break;
            case "4": strtempe = "公费医疗"; break;
            case "5": strtempe = "大病统筹"; break;
            default: strtempe = "其他"; 
                break;
        }
        return strtempe;
    }
    /// <summary>
    /// 是否婚姻
    /// </summary>
    /// <param name="typeid"></param>
    /// <returns></returns>
    private string GetMarry(string typeid)
    {
        string strtempe = string.Empty;
        switch (typeid)
        {
            case "1": strtempe = "未婚"; break;
            case "2": strtempe = "已婚"; break;
            case "3": strtempe = "丧偶"; break;
            case "4": strtempe = "离婚"; break;
            case "5": strtempe = "其他"; break;
            default:
                break;
        }
        return strtempe;
    }
    /// <summary>
    /// 入院途径
    /// </summary>
    /// <param name="typeid"></param>
    /// <returns></returns>
    private string GetInType(string typeid)
    {
        string strtempe = string.Empty;
        switch (typeid)
        {
            case "1": strtempe = "门诊"; break;
            case "2": strtempe = "急诊"; break;
            case "3": strtempe = "其他医疗机构转入"; break;
            default: strtempe = "其他医疗机构转入";
                break;
        }
        return strtempe;
    }
    #region 整形绑定关联值
    /// <summary>
    /// 获取主要诊断
    /// </summary>
    /// <param name="id">条件参数</param>
    /// <returns></returns>
    public string GetZD(string id, string PA_BINGAN_CODE, string PA_BEINHOSPITAL_GREE)
    {
        
        string strQuery = string.Format("SELECT * FROM AR_FP_DIAGNOSE WHERE AR_FP_DI_ID='{0}' AND AR_FP_DI_HOSPITAL='{1}_{2}'", id, PA_BINGAN_CODE, PA_BEINHOSPITAL_GREE);
        DataSet dataSet = sql_help.Query(strQuery);
        string sqlZD = string.Empty;
        string sqlZD1_28 = string.Empty;
        string sqlZD1_29 = string.Empty;
        string sqlZD2 = string.Empty;

        int rowCount=dataSet.Tables[0].Rows.Count;
        
        for (int i = 0; i < 16; i++)
        {
            if (i < rowCount)
            {
                DataRow dataRow = dataSet.Tables[0].Rows[i];
                int r = int.Parse(dataRow["AR_FP_DI_TYPE"].ToString().ToString());
                if (r < 27)
                {
                    sqlZD = sqlZD + "','" + dataRow["AR_FP_DI_NAME"].ToString();
                    sqlZD = sqlZD + "','" + dataRow["AR_FP_DI_ICD"].ToString();
                    sqlZD = sqlZD + "','" + dataRow["AR_FP_DI_DISCHARGECONDITIONS"].ToString();
                }
            }
            else {
                sqlZD = sqlZD + "','" + "";
                sqlZD = sqlZD + "','" + "";
                sqlZD = sqlZD + "','" + "";
            }
        }
       
         //第二
         for (int i1 = 0; i1 < dataSet.Tables[0].Rows.Count; i1++)
         {
             DataRow dataRow = dataSet.Tables[0].Rows[i1];
             int r = int.Parse(dataRow["AR_FP_DI_TYPE"].ToString().ToString());
             if (r ==28)
             {
                 sqlZD1_28 = sqlZD1_28 + "','" + dataRow["AR_FP_DI_NAME"].ToString();
                 sqlZD1_28 = sqlZD1_28 + "','" + dataRow["AR_FP_DI_ICD"].ToString();
             }
             else if (r == 29)
             {
                 sqlZD1_29 = sqlZD1_29 + "','" + dataRow["AR_FP_DI_NAME"].ToString();
                 sqlZD1_29 = sqlZD1_29 + "','" + dataRow["AR_FP_DI_ICD"].ToString();
             }
             else if (r == 29)
             {
                 sqlZD2 = sqlZD1_29 + "','" + dataRow["AR_FP_DI_ICD"].ToString();
             }
         }
        
         if (sqlZD2.Equals(string.Empty))
             sqlZD2 = "','";
         if (sqlZD1_28.Equals(string.Empty))
             sqlZD2 = "','','";
         if (sqlZD1_29.Equals(string.Empty))
             sqlZD2 = "','','";

         return sqlZD + sqlZD1_28 + sqlZD1_29 + sqlZD2;
    }


    /// <summary>
    /// 获取手术信息
    /// </summary>
    /// <param name="id">条件参数</param>
    /// <returns></returns>
    public string GetSSInfo(string id, string PA_BINGAN_CODE, string PA_BEINHOSPITAL_GREE)
    {
        
        string strQuery = string.Format("SELECT * FROM AR_FP_OPERATION WHERE AR_FP_OP_ID='{0}' AND AR_FP_OP_HOSPITAL='{1}_{2}'", id, PA_BINGAN_CODE, PA_BEINHOSPITAL_GREE);
        DataSet dataSet = sql_help.Query(strQuery);
        string sqlZD = string.Empty;

        int rowCount = dataSet.Tables[0].Rows.Count;

        for (int i = 0; i < 7; i++)
        {
            if (i < rowCount)
            {
                DataRow dataRow = dataSet.Tables[0].Rows[i];
                sqlZD =sqlZD+"','"+ dataRow["AR_FP_OP_CODES"].ToString();
                sqlZD = sqlZD + "','" + DateTime.Parse(dataRow["AR_FP_OP_DATE"].ToString()).ToString("yyyyMMdd");
                sqlZD = sqlZD + "','" + dataRow["AR_FP_OP_OPLEVEL"].ToString();
                sqlZD = sqlZD + "','" + dataRow["AR_FP_OP_NAME"].ToString();
                sqlZD = sqlZD + "','" + dataRow["AR_FP_OP_THESURGERY"].ToString();
                sqlZD = sqlZD + "','" + dataRow["AR_FP_OP_PR_ASSISTANT"].ToString();
                sqlZD = sqlZD + "','" + dataRow["AR_FP_OP_SE_ASSISTANT"].ToString();
                sqlZD = sqlZD + "','" + dataRow["AR_FP_OP_HEALINGLEVEL"].ToString();//切口等级
                sqlZD = sqlZD + "','" + dataRow["AR_FP_OP_HEALINGLEVEL"].ToString();//切口愈合类别
                sqlZD = sqlZD + "','" + dataRow["AR_FP_OP_ANESTHESIA"].ToString();//麻醉方式
                sqlZD = sqlZD + "','" + dataRow["AR_FP_OP_ANANESTHESIOLOGIST"].ToString();//麻醉医师
            }
            else
            {
                sqlZD = sqlZD + "','" + "','','','','','','','','','','";
            }
        }
        return sqlZD;
    }
    #endregion
}

}
